﻿package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.User;

public class UserDao {

	public UserDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";
	//String pass = "webclass";


	// 회원가입
	public boolean insertMember(User newUser) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into user(memid, passwd, name, email, pNum, point, univ, grade, langid1, langGrade1,"
							+ " langid2, langGrade2, langid3, langGrade3) values(?,?,?,?,?,0,?,?,?,?,?,?,?,?)");

			pstmt.setString(1, newUser.getMemid());
			pstmt.setString(2, newUser.getPasswd());
			pstmt.setString(3, newUser.getName());
			pstmt.setString(4, newUser.getEmail());
			pstmt.setString(5, newUser.getpNum());
			if (newUser.getUniv() != null) {
				pstmt.setString(6, newUser.getUniv());
				pstmt.setString(7, newUser.getGrade());
			} else {
				pstmt.setString(6, null);
				pstmt.setString(7, null);
			}
			if (newUser.getLangid1() == 0) {
				pstmt.setInt(8, newUser.getLangid1());
				pstmt.setInt(9, 0);
			} else {
				pstmt.setInt(8, newUser.getLangid1());
				pstmt.setInt(9, newUser.getLangGrade1());
			}
			if (newUser.getLangid2() == 0) {
				pstmt.setInt(10, newUser.getLangid2());
				pstmt.setInt(11, 0);
			} else {
				pstmt.setInt(10, newUser.getLangid2());
				pstmt.setInt(11, newUser.getLangGrade2());
			}
			if (newUser.getLangid3() == 0) {
				pstmt.setInt(12, newUser.getLangid3());
				pstmt.setInt(13, 0);
			} else {
				pstmt.setInt(12, newUser.getLangid3());
				pstmt.setInt(13, newUser.getLangGrade3());
			}

			pstmt.executeUpdate();
			
			String uid = getUserIdByMemId(newUser.getMemid());
			UserDetailDao dao = new UserDetailDao();
			dao.insertUserDetailInfo(Integer.parseInt(uid));
			return true;

		} catch (Exception ex) {
			ex.printStackTrace();
			return false;

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}
		

	}

	// 회원 정보 수정
	public void updateMemberInfo(User updateUser) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update user set passwd=?, email=?, pNum=?, univ=?, grade=?, langid1=?, langGrade1=?,"
							+ " langid2=?, langGrade2=?, langid3=?, langGrade3=? where userid=?");

			pstmt.setString(1, updateUser.getPasswd());
			pstmt.setString(2, updateUser.getEmail());
			pstmt.setString(3, updateUser.getpNum());
			if (updateUser.getUniv() != null) {
				pstmt.setString(4, updateUser.getUniv());
				pstmt.setString(5, updateUser.getGrade());
			} else {
				pstmt.setString(4, null);
				pstmt.setString(5, null);
			}
			if (updateUser.getLangid1() == 0) {
				pstmt.setInt(6, updateUser.getLangid1());
				pstmt.setInt(7, 0);
			} else {
				pstmt.setInt(6, updateUser.getLangid1());
				pstmt.setInt(7, updateUser.getLangGrade1());
			}
			if (updateUser.getLangid2() == 0) {
				pstmt.setInt(8, updateUser.getLangid2());
				pstmt.setInt(9, 0);
			} else {
				pstmt.setInt(8, updateUser.getLangid2());
				pstmt.setInt(9, updateUser.getLangGrade2());
			}
			if (updateUser.getLangid3() == 0) {
				pstmt.setInt(10, updateUser.getLangid3());
				pstmt.setInt(11, 0);
			} else {
				pstmt.setInt(10, updateUser.getLangid3());
				pstmt.setInt(11, updateUser.getLangGrade3());
			}
			pstmt.setInt(12, updateUser.getUserid());

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 아이디, 패스워드 확인
	public boolean isLogin(String memid, String passwd) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select * from user where memid=? and passwd=?");
			pstmt.setString(1, memid);
			pstmt.setString(2, passwd);
			rs = pstmt.executeQuery();

			if (rs.next()) {
				return true;
			} else {

				return false;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 아이디 중복확인
	public boolean isCheckedDuplicatedId(String memid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con.prepareStatement("select * from user where memid=?");
			pstmt.setString(1, memid);
			rs = pstmt.executeQuery();

			if (rs.next()) {
				return false;
			} else {

				return true;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 멤버아이디로 유저번호 가져오기
	public String getUserIdByMemId(String memid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select userid from user where memid=?");
			pstmt.setString(1, memid);
			rs = pstmt.executeQuery();

			if (rs.next()) {
				return rs.getString("userid").trim();
			} else {

				return null;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// user id 넘겼을 때 가입한 스터디아이디 가져오기
	public ArrayList<Integer> getUserJoinedStudyInfo(int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Integer> joinedStudy = new ArrayList<Integer>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select sid from participation where isConfirmed=1 and uid=?");
			pstmt.setInt(1, uid);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				int sid = rs.getInt("sid");
				joinedStudy.add(sid);
			}
			return joinedStudy;

		} catch (Exception ex) {
			ex.printStackTrace();
			return joinedStudy;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	public User getUserInfo(int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		User currentUser = new User();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select userid,memid, passwd, name, email, pNum, point, univ, grade, langid1, langGrade1,"
							+ " langid2, langGrade2, langid3, langGrade3 from user where userid=?");
			pstmt.setInt(1, uid);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				currentUser.setUserid(rs.getInt("userid"));
				currentUser.setMemid(rs.getString("memid"));
				currentUser.setPasswd(rs.getString("passwd"));
				currentUser.setName(rs.getString("name"));
				currentUser.setEmail(rs.getString("email"));
				currentUser.setpNum(rs.getString("pNum"));
				currentUser.setPoint(rs.getInt("point"));
				currentUser.setUniv(rs.getString("univ"));
				currentUser.setGrade(rs.getString("grade"));
				currentUser.setLangid1(rs.getInt("langid1"));
				currentUser.setLangGrade1(rs.getInt("langGrade1"));
				currentUser.setLangid2(rs.getInt("langid2"));
				currentUser.setLangGrade2(rs.getInt("langGrade2"));
				currentUser.setLangid3(rs.getInt("langid3"));
				currentUser.setLangGrade3(rs.getInt("langGrade3"));
			}
			return currentUser;

		} catch (Exception ex) {
			ex.printStackTrace();
			return currentUser;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 포인트 충전
	public void chargePoint(String memid, int point) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update user set point=point+? where memid=?");

			pstmt.setInt(1, point);
			pstmt.setString(2, memid);
			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

}